import sys
import sqlite3

from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtSql import *

import SalarySource

class SalaryWindows(QMainWindow):
    """a simple salary statistics software"""
    def __init__(self, parent = None):
        """setup Ui and connect Singal/Slot"""
        QMainWindow.__init__(self, parent)
        self.__setupUi()
        self.__connectDatabase()
        self.__showInitData()
        self.__connectSingalSlot()

    def __setupUi(self):
        """widgets, layout etc."""
        #window title
        self.setWindowTitle(self.tr("SalaryStatistics"))

        #toolbar
        self.toolBar = QToolBar(self)
        self.addItemAction = self.toolBar.addAction(QIcon(":/icons/add.bmp"), "")
        self.delItemAction = self.toolBar.addAction(QIcon(":/icons/del.bmp"), "")
        self.saveItemAction = self.toolBar.addAction(QIcon(":/icons/save.bmp"), "")
        self.revertItemAction = self.toolBar.addAction(QIcon(":/icons/revert.bmp"), "")
        self.backupItemAction = self.toolBar.addAction(QIcon(":/icons/backup.bmp"), "")
        self.recoverItemAction = self.toolBar.addAction(QIcon(":/icons/recover.bmp"), "")

        #tableview
        self.tableView = QTableView(self)
        self.tableView.setMinimumSize(400, 300)

        #curve

        #main layout
        mainLayout = QVBoxLayout()
        mainLayout.addWidget(self.toolBar)
        mainLayout.addWidget(self.tableView)

        #central widget
        centralWidget = QWidget()
        centralWidget.setLayout(mainLayout)
        self.setCentralWidget(centralWidget)

    def __connectDatabase(self):
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName('SalaryStatistics.db')
        if not self.db.open():
            print "database open fail, exit."
        query = QSqlQuery()
        query.exec_("CREATE TABLE IF NOT EXISTS Salary(Year INT PRIMARY KEY, Month INT, Salary INT)")

    def __showInitData(self):
        self.dataModel = QSqlTableModel(self, self.db)
        self.dataModel.setTable("Salary")
        self.dataModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.dataModel.select()
        self.tableView.setModel(self.dataModel)

    def __connectSingalSlot(self):
        QObject.connect(self.addItemAction, SIGNAL("triggered(bool)"), self.__addSalaryItem)
        QObject.connect(self.delItemAction, SIGNAL("triggered(bool)"), self.__delSalaryItem)
        QObject.connect(self.saveItemAction, SIGNAL("triggered(bool)"), self.__saveSalaryItem)
        QObject.connect(self.revertItemAction, SIGNAL("triggered(bool)"), self.__revertSalaryItem)
        QObject.connect(self.backupItemAction, SIGNAL("triggered(bool)"), self.__backupSalaryItem)
        QObject.connect(self.recoverItemAction, SIGNAL("triggered(bool)"), self.__recoverSalaryItem)

    def __addSalaryItem(self):
        self.dataModel.insertRow(self.dataModel.rowCount())

    def __delSalaryItem(self):
        self.dataModel.removeRow(self.tableView.currentIndex().row())

    def __saveSalaryItem(self):
        self.dataModel.database().transaction()
        if self.dataModel.submitAll():
            self.dataModel.database().commit()
        else:
            self.dataModel.database().rollback()

    def __revertSalaryItem(self):
        self.dataModel.revertAll()
        
    def __backupSalaryItem(self):
        sqliteCon = sqlite3.connect('SalaryStatistics.db')
        with sqliteCon:
            data = '\n'.join(sqliteCon.iterdump())
            f = open('salary.sql', 'w')
            with f:
                f.write(data)
        
    def __recoverSalaryItem(self):
        sqliteCon = sqlite3.connect('SalaryStatistics.db')
        with sqliteCon:
            f = open('salary.sql', 'r')
            with f:
                sqliteCon.cursor().execute("DROP TABLE IF EXISTS Salary")
                sqliteCon.cursor().executescript(f.read())
                self.dataModel.select()

if __name__ == "__main__":
    app = QApplication(sys.argv) 
    #translator
    translator = QTranslator()
    translator.load("SalaryStatistics_" + QLocale.system().name(), ":/translations")
    app.installTranslator(translator)
    #show window
    w = SalaryWindows()
    w.show() 
    sys.exit(app.exec_())
